/**
 * 站点基础数据导入脚本
 * 功能：
 * 1) 读取 .env（如存在）与默认配置，连接远程 MySQL 8.0
 * 2) 创建站点基础数据表（如不存在）
 * 3) 读取 ./output/websocket_data_100_site.json，处理站点数据格式
 * 4) 批量导入（存在则更新）
 */

const fs = require('fs');
const path = require('path');
require('dotenv').config();
const mysql = require('mysql2/promise');

// 数据库配置
const dbConf = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT || '3306'),
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASSWORD || 'yEAiRE2jcxTyRBy5',
    database: process.env.DB_NAME || 'nextjs',
    charset: 'utf8mb4',
    multipleStatements: true
};

const INPUT_FILE = path.join(__dirname, 'output', 'websocket_data_100_site.json');

// 站点类型映射
const SITE_TYPE_MAP = {
    1: '村庄',
    2: '密穴',
    3: '城市',
    4: '野外',
    5: '副本',
    6: '主城',
    7: '乱葬岗',
    8: '特殊',
    9: '副本地图',
    10: '桥梁',
    11: '洞穴',
    12: '军营',
    13: '要塞',
    14: '港口',
    15: '关隘',
    16: '宫殿'
};

function safeInt(v) {
    if (v === undefined || v === null) return null;
    const n = parseInt(String(v).replace(/[^0-9-]/g, ''), 10);
    return Number.isNaN(n) ? null : n;
}

async function ensureTable(conn) {
    // 先删除旧表（如果存在）
    await conn.execute('DROP TABLE IF EXISTS site_base');

    const sql = `
        CREATE TABLE site_base (
            id INT NOT NULL PRIMARY KEY COMMENT '站点ID',
            name VARCHAR(100) NOT NULL COMMENT '站点名称',
            act_id INT NULL COMMENT '活动ID',
            area_id INT NULL COMMENT '区域ID',
            img_type INT NULL COMMENT '图片类型',
            num INT NULL COMMENT '数量',
            ss INT NULL COMMENT 'SS值',
            type_id INT NULL COMMENT '站点类型ID',
            type_name VARCHAR(50) NULL COMMENT '站点类型名称',
            raw_json JSON NULL COMMENT '原始数据',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='站点基础数据表';
    `;
    await conn.execute(sql);
}

async function importData() {
    if (!fs.existsSync(INPUT_FILE)) {
        console.error('未找到数据文件:', INPUT_FILE);
        process.exit(1);
    }

    const rawObj = JSON.parse(fs.readFileSync(INPUT_FILE, 'utf8'));

    const conn = await mysql.createConnection(dbConf);
    try {
        console.log('✅ 已连接 MySQL:', `${dbConf.user}@${dbConf.host}:${dbConf.port}/${dbConf.database}`);
        await ensureTable(conn);

        const upsertSql = `
            INSERT INTO site_base
                (id, name, act_id, area_id, img_type, num, ss, type_id, type_name, raw_json)
            VALUES
                (?, ?, ?, ?, ?, ?, ?, ?, ?, CAST(? AS JSON))
            ON DUPLICATE KEY UPDATE
                name=VALUES(name),
                act_id=VALUES(act_id),
                area_id=VALUES(area_id),
                img_type=VALUES(img_type),
                num=VALUES(num),
                ss=VALUES(ss),
                type_id=VALUES(type_id),
                type_name=VALUES(type_name),
                raw_json=VALUES(raw_json),
                updated_at=CURRENT_TIMESTAMP
        `;

        let count = 0;
        for (const [siteName, siteData] of Object.entries(rawObj)) {
            if (!siteData || typeof siteData !== 'object') continue;
            
            const id = safeInt(siteData.id);
            const name = siteData.name || siteName;
            const actId = safeInt(siteData.act_id);
            const areaId = safeInt(siteData.area_id);
            const imgType = safeInt(siteData.imgType);
            const num = safeInt(siteData.num);
            const ss = safeInt(siteData.ss);
            const typeId = safeInt(siteData.type_id);
            const typeName = SITE_TYPE_MAP[typeId] || '未知';

            if (!id || !name) continue;

            const rawJson = JSON.stringify(siteData);
            await conn.execute(upsertSql, [
                id,
                name,
                actId,
                areaId,
                imgType,
                num,
                ss,
                typeId,
                typeName,
                rawJson
            ]);
            count++;
        }

        console.log(`✅ 导入完成，共处理 ${count} 条记录`);
        
        // 显示统计信息
        const [stats] = await conn.execute(`
            SELECT 
                type_name,
                COUNT(*) as count,
                MIN(area_id) as min_area_id,
                MAX(area_id) as max_area_id
            FROM site_base 
            GROUP BY type_id, type_name
            ORDER BY type_id
        `);
        
        console.log('\n📊 导入统计:');
        console.table(stats);
        
        // 按区域统计
        const [areaStats] = await conn.execute(`
            SELECT 
                area_id,
                COUNT(*) as site_count,
                GROUP_CONCAT(DISTINCT type_name ORDER BY type_name SEPARATOR ', ') as types
            FROM site_base 
            WHERE area_id IS NOT NULL
            GROUP BY area_id
            ORDER BY area_id
            LIMIT 10
        `);
        
        console.log('\n📊 按区域统计 (前10个):');
        console.table(areaStats);
        
    } finally {
        await conn.end();
    }
}

if (require.main === module) {
    importData().catch((err) => {
        console.error('导入失败:', err.message);
        process.exit(1);
    });
}

module.exports = { importData };

